package org.languageguide.readings;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.languageguide.storage.DBConnector;
import org.languageguide.user.User;

public class ReadingManager {

	public static void addVote(String idStr, String voteStr, User user){
		Integer id = Integer.parseInt(idStr);
		Integer vote = Integer.parseInt(voteStr);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		String sql = "SELECT * FROM ReadingVotes where ReadingId = ? AND UserId = ?";
		PreparedStatement statement;
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			statement.setInt(2, user.getIdInteger());
			ResultSet set = statement.executeQuery();
			if (set.next()){
				Integer oldVote = set.getInt("Vote");
				if (oldVote == vote){
					return;
				}
				else{
					sql = "Update ReadingsVote Set Vote = ? WHERE UserId = ? AND ReadingId = ?";
					statement = connection.prepareStatement(sql);
					statement.setInt(1, vote);		
					statement.setInt(2, user.getIdInteger());
					statement.setInt(3, id);
					statement.executeUpdate();
					calculateNewVote(id, connection);
				}
			}
			else{
				sql = "INSERT INTO Vote (Vote, UserId, ReadingId) VALUES(?, ?, ?)";
				statement = connection.prepareStatement(sql);
				statement.setInt(1, vote);
				statement.setInt(2, user.getId());
				statement.setInt(3, id);
				statement.executeUpdate();
				calculateNewVote(id, connection);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connector.close(connection);
		}
	}

	public static void calculateNewVote(Integer id, Connection connection) throws SQLException{
		String sql = "SELECT * FROM ReadingVotes WHERE ReadingId = ?";

		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet set = statement.executeQuery();
		Integer sumOfAllVotes = 0;
		int count = 0;
		while (set.next()){
			Integer vote = set.getInt("Vote");
			sumOfAllVotes += vote;
			count++;
		}

		double average = sumOfAllVotes / count;
		sql = "Update Reading Set VoteAverage = ? WHERE Id = ? ";
		statement = connection.prepareStatement(sql);
		statement.setDouble(1, average);
		statement.setInt(2, id);
		statement.executeUpdate();


	}

	public static void saveDefinition(Integer readingId, String text, String type){
		String sql = "INSERT INTO ReadingDefinitions(ReadingId, MatchingText, Type) VALUES(?, ?, ?)";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setInt(1, readingId);
			statement.setString(2, text.trim());
			if (type.equals("null")){
				statement.setString(3, null);
			}
			else{
				statement.setString(3, type);
			}
			statement.executeUpdate();
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
	}

	public static TranslatedReading getReading(String id, String targetLanguage){
		TranslatedReading reading = new TranslatedReadingImpl(id, targetLanguage);
		return reading;
	}

	public static Reading getReading(String id){
		Reading reading = new ReadingImpl(id);
		return reading;
	}

	/*public static void setStatus(String id, String status){
		String sql = "Update Readings Set Status=\"" + status + "\" WHERE Id=" + id;

	}*/

	public static int saveTimes(String id, String times, String adjustedWordIds, User user){
		return saveTimes(id, times, adjustedWordIds, null, user);
	}

	public static int saveTimes(String id, String times, String adjustedWordIds, String status, User user){
		String adjustedWordIdInsertionClause;
		//String highestIdProcessedInsertionClause;
		if (adjustedWordIds != null){
			adjustedWordIdInsertionClause = ", AdjustedWordIds=\"" + adjustedWordIds + "\"";
		}
		else adjustedWordIdInsertionClause = "";

		/*if (highestIdProcessed != null){
			highestIdProcessedInsertionClause = ", HighestIdProcessed=" + highestIdProcessed;
		}
		else highestIdProcessedInsertionClause = "";*/

		Integer userId = user.getIdInteger();
		String sql;
		if (status != null){
			sql = "Update Readings Set Times=\"" + times + "\"" + adjustedWordIdInsertionClause +  " , Status=\"" + status + "\", UserId=\"" + userId + "\" WHERE Id=" + id;
		}
		else{
			sql = "Update Readings Set Times=\"" + times + "\"" + adjustedWordIdInsertionClause + ", UserId=\"" + userId + "\" WHERE Id=" + id;			
		}
		//System.out.println("sql" + sql);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			Statement statement = connection.createStatement();
			return statement.executeUpdate(sql);
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return 0;	

	}

	/*public static String getWordWrappedReading(String id, String variablePrefix){

		String sql = "SELECT Text FROM Readings WHERE Id=" + id;
		System.out.println(sql);
		Connector connector = null;
		Statement statement;
		try{
			connector = new Connector();
			statement = connector.open();
			ResultSet set = statement.executeQuery(sql);
			while (set.next()){
				String text = set.getString(1);
				String wordWrappedReading = new WordWrapper().prepare(text, variablePrefix);
				return wordWrappedReading;
			}
			return "reading not found";
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			if (connector != null)
				connector.close();
		}
		return null;
	}*/

	public static List<Reading> getTranslatedVideos(String language, String targetLanguage){
		String sql ="SELECT DISTINCT Readings.* FROM ReadingSentences " + 
				"LEFT JOIN ReadingSentenceTranslations ON ReadingSentenceTranslations.ReadingSentencesId = ReadingSentences.Id AND ReadingSentenceTranslations.TargetLanguage = ? " +
				"LEFT JOIN Readings ON Readings.Id = ReadingSentences.ReadingId " +
				"WHERE Readings.IsVideo = 1 AND Language = ?";
		List<Reading> list = new ArrayList<Reading>();
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, targetLanguage);
			statement.setString(2, language);
			statement.executeQuery();
			ResultSet set = statement.executeQuery();
			while (set.next()){
				Reading reading = new ReadingImpl(set);
				list.add(reading);
			}
			return list;
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
	}


	public static List<TranslatedReading> getReadings(String language, String targetLanguage, boolean languageGuide){
		String sql = "SELECT * FROM Readings LEFT JOIN TranslatedReading ON Readings.Id = TranslatedReading.ReadingId AND TargetLanguage = ? WHERE Language = ? AND Active = 1 AND LanguageGuide = ? ORDER BY Rating DESC";
		System.out.println(sql);
		List <TranslatedReading> list = new ArrayList<TranslatedReading>();
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, targetLanguage);
			statement.setString(2, language);
			statement.setBoolean(3, languageGuide);
			ResultSet set = statement.executeQuery();
			while (set.next()){
				TranslatedReading reading = new TranslatedReadingImpl(set, targetLanguage);
				list.add(reading);
			}
			return list;
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
	}

	public static List<TranslatedReading> getPublishedReadings(String language, String targetLanguage, boolean languageGuide){
		String sql = "SELECT * FROM Readings LEFT JOIN TranslatedReading ON Readings.Id = TranslatedReading.ReadingId AND TargetLanguage = ? WHERE Language = ? AND Active = 1 AND LanguageGuide = ? AND PublishedText IS NOT NULL ORDER BY Rating DESC";
		System.out.println(sql);
		List <TranslatedReading> list = new ArrayList<TranslatedReading>();
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, targetLanguage);
			statement.setString(2, language);
			statement.setBoolean(3, languageGuide);
			ResultSet set = statement.executeQuery();
			while (set.next()){
				TranslatedReading reading = new TranslatedReadingImpl(set, targetLanguage);
				list.add(reading);
			}
			return list;
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
	}


	static String pTagEndAndStart =  "</p>\r\n<p>";
	public static String addParagraghTagsIfMissing(String reading){
		int loc = reading.indexOf("<p>");
		if (loc == -1){
			reading = "<p>" + reading + "</p>";
			loc = reading.indexOf("\r\n\r\n");
			int length = reading.length();
			System.out.println("length" + length);
			System.out.println("loc" + loc);
			while (loc != -1){
				reading = reading.substring(0 , loc) + pTagEndAndStart + reading.substring(loc + 4);
				System.out.println("reading" + reading);
				int startLoc = loc + pTagEndAndStart.length()  +1;
				loc = reading.indexOf("\r\n\r\n", startLoc);
				System.out.println("loc" + loc);
			}
		}
		return reading;

	}

	public static void editReading(Integer id, String reading, String title, String language, String source, boolean hasWordGranularity, String comments){

		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			String updateSql = "UPDATE Readings Set Title = ?, Text = ?, Language = ?, WordWrappedText = ?, WordCount = ?, SentenceWrappedText = ?, SentenceCount = ?,  Source = ?, HasWordGranularity = ?, Comments = ? WHERE Id = ?";
			PreparedStatement ps = connection.prepareStatement(updateSql);
			storeReading(ps, reading, title, language, source, hasWordGranularity, comments);
			ps.setInt(11, id);
			ps.executeUpdate();
		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
	}


	public static void storeSentencesToTranslate(Integer readingId){
		String sql = "SELECT * from Readings WHERE Id = ?";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, readingId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()){
				String text = rs.getString("Text");
				SentenceWrapper wrapper = new StandardSentenceWrapper();
				wrapper.wrap(text);
				wrapper.storeSentencesInDb(readingId);
			}

		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}		
	}


	public static void storeWordWrappedText(Integer readingId){
		String sql = "SELECT * from Readings WHERE Id = ?";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, readingId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()){
				String text = rs.getString("Text");
				String updateSql = "UPDATE Readings Set WordWrappedText = ?, WordCount = ? WHERE Id = ?";
				ps = connection.prepareStatement(updateSql);
				WordWrapper wrappedReading = new StandardWordWrapper();
				String wordWrapped = wrappedReading.wrap(text);
				Integer wordCount = wrappedReading.getWordCount();
				ps.setString(1, wordWrapped);
				ps.setInt(2, wordCount);
				ps.setInt(3, readingId);
				ps.executeUpdate();
				//wrapper.storeSentencesInDb(readingId);
			}

		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}		
	}


	public static void storeTextFromSentences(Integer readingId){
		String sql = "SELECT * from Readings WHERE Id = ?";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, readingId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()){
				String text = rs.getString("Text");
				String updateSql = "UPDATE Readings Set WordWrappedText = ?, WordCount = ? WHERE Id = ?";
				ps = connection.prepareStatement(updateSql);
				WordWrapper wrappedReading = new StandardWordWrapper();
				String wordWrapped = wrappedReading.wrap(text);
				Integer wordCount = wrappedReading.getWordCount();
				ps.setString(1, wordWrapped);
				ps.setInt(2, wordCount);
				ps.setInt(3, readingId);
				ps.executeUpdate();
				//wrapper.storeSentencesInDb(readingId);
			}

		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}		
	}



	public static void storeSentenceWrappedText(Integer readingId){
		String sql = "SELECT * from Readings WHERE Id = ?";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, readingId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()){
				String text = rs.getString("Text");
				SentenceWrapper wrapper = new StandardSentenceWrapper();
				String wrappedText = wrapper.wrap(text);
				String updateSql = "UPDATE Readings Set SentenceWrappedText = ? WHERE Id = ?";
				ps = connection.prepareStatement(updateSql);
				ps.setString(1, wrappedText);
				ps.setInt(2, readingId);
				ps.executeUpdate();
				//wrapper.storeSentencesInDb(readingId);
			}

		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}		
	}

	public static void addTranslation(HttpSession session,String translation, String target, User user){
		translation = translation.trim();
		translation = addParagraghTagsIfMissing(translation);
		SentenceWrapper wrapper = new StandardSentenceWrapper();
		List<Entity> paragraphs = wrapper.getParagraphsAndSentences(translation);
		List<String> sentences = convertParagraphsIntoOnlySenteces(paragraphs);
		session.setAttribute("reading_translations", sentences);

	}

	public static List<String> getTranslationsFromSession(HttpSession session){
		List<String> sentences = (List<String>)session.getAttribute("reading_translations");
		return sentences;
	}

	public static List<String> convertParagraphsIntoOnlySenteces(List<Entity> paragraphs){
		List<String> allSentences = new ArrayList<String>();
		for (Entity paragraph: paragraphs){
			List<String> sentences = paragraph.getSentences();
			allSentences.addAll(sentences);
		}
		return allSentences;
	}


	public void storeTranslationsInDb(Integer readingId, String targetLanguage, List<Entity> paragraphs){

		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			connection.setAutoCommit(false);

			String sql = "INSERT INTO ReadingSentenceTranslations(Paragraph, SentenceOrder, Sentence, ReadingId, TargetLanguage) VALUES(?, ?, ?, ?, ?)";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			int paragraphCount = 1;
			int sentenceCount = 1;
			for (Entity entity: paragraphs){
				List<String> sentences = entity.getSentences();
				for (String sentence: sentences){
					//sentence = "P.S. Si vous me donnez ladresse de Monsieur Crisse, je lui enverrai une carte postale.";
					//sentence = "test";
					//
					// Create a prepared statement


					// Insert 10 rows of data

					pstmt.setInt(1, paragraphCount);
					pstmt.setInt(2, sentenceCount);
					pstmt.setString(3, sentence);
					pstmt.setInt(4, readingId);
					pstmt.setString(5, targetLanguage);
					pstmt.addBatch();
					sentenceCount++;
				}
				paragraphCount++;
			}
			pstmt.executeBatch();
			connection.setAutoCommit(true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}


	public static Integer addReading(String reading, String title, String language, String source, boolean hasWordGranularity, String comments, User user){
		int userId = user.getId();
		System.out.println("userId" + userId);
		String sql = "INSERT INTO Readings (Title, Text, Language, WordWrappedText, WordCount, SentenceWrappedText, SentenceCount, Source, HasWordGranularity, Status, Comments, UserId) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, \"text-submited\", ?, ?)";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			SentenceWrapper wrapper = storeReading(ps, reading, title, language, source, hasWordGranularity, comments);
			ps.setInt(11, userId);
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				Integer id = rs.getInt(1);
				wrapper.storeSentencesInDb(id);
				//ps.close();
				return id;
			} 
			else {
				System.out.println("There are no generated keys.");
			}
			//ps.close();

		}
		catch (SQLException e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}

		return null;
	}

	private static SentenceWrapper storeReading(PreparedStatement ps, String reading, String title, String language, String source, boolean hasWordGranularity, String comments) throws SQLException{
		//WordWrapper preparor = new WordWrapper();
		reading = reading.trim();
		reading = addParagraghTagsIfMissing(reading);

		ps.setString(1, title);
		ps.setString(2, reading);
		ps.setString(3, language);

		if (hasWordGranularity){
			WordWrapper wrappedReading = new StandardWordWrapper();
			String wordWrapped = wrappedReading.wrap(reading);
			Integer wordCount = wrappedReading.getWordCount();
			ps.setString(4, wordWrapped);
			ps.setInt(5, wordCount);
		}
		else{
			ps.setNull(4, java.sql.Types.VARCHAR);
			ps.setNull(5, java.sql.Types.INTEGER);
		}

		SentenceWrapper wrapper = new StandardSentenceWrapper();
		String sentenceWrapped = wrapper.wrap(reading);

		Integer sentenceCount = wrapper.getSentenceCount();

		ps.setString(6, sentenceWrapped);
		ps.setInt(7, sentenceCount);
		ps.setString(8, source);
		ps.setBoolean(9, hasWordGranularity);
		ps.setString(10, comments);
		return wrapper;
	}

	private String processSentence(String sentence, int order){
		String sentenceStart = sentence.substring(0, sentence.length() - 1);
		String sentenceEnd = sentence.substring(sentence.length() - 1);
		if (sentenceEnd.equals("\"") || sentenceEnd.equals(")")){
			String charEnclosingSentence = sentenceEnd;
			sentenceEnd = sentence.substring(sentence.length() - 2, sentence.length() - 1);
			sentenceStart = sentence.substring(0, sentence.length() - 2);
			return "<span id=\"s" + order + "\" class=\"s\">" +  sentenceStart + "<span class=\"translation\" id=\"" + order + "\">" + sentenceEnd + "</span>" + charEnclosingSentence + "</span> ";
		}
		return "<span id=\"s" + order + "\" class=\"s\">" +  sentenceStart + "<span class=\"translation\" id=\"" + order + "\">" + sentenceEnd + "</span></span> "; 
	}

	private String processSentenceSimple(String sentence, int order){
		//String sentenceStart = sentence.substring(0, sentence.length() - 1);
		//String sentenceEnd = sentence.substring(sentence.length() - 1);
		return "<span id=\"s" + order + "\">" + sentence + "</span> "; 
	}

	
	private TreeMap<Integer, String> loadImages(Integer id){
		TreeMap<Integer, String> imageMap = new TreeMap<Integer, String>();
		String sql_load = "SELECT * FROM ReadingImages WHERE ReadingId = " + id + " ORDER BY Paragraph ASC";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection(false);
		try {
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql_load);
			int order = 1;
			while (set.next()){
				String imageSrc = set.getString("ImageSrc");
				Integer width = set.getInt("Width");
				Integer height = set.getInt("Height");
				String style = set.getString("Style");
				String align = set.getString("Align");
				if (style == null)
					style ="";
				String imageStr;
				if (align.equals("right")){
					imageStr = "<img id=\"image" + order++ + "\" src=\"" + imageSrc + "\" width=\"" + width + "\" height=\"" + height + "\" align=\"right\" style=\"margin-left: 10px;" + style + "\">";
				}
				else{
					imageStr = "<img id=\"image" + order++ + "\" src=\"" + imageSrc + "\" width=\"" + width + "\" height=\"" + height + "\" align=\"left\" style=\"margin-right: 10px;" + style + "\">";
				}
				String href = set.getString("HREF");
				if (href != null){
					imageStr = "<a href=\"" + href + "\">" + imageStr + "</a>";
				}
				Integer paragraph = set.getInt("Paragraph");
				imageMap.put(paragraph, imageStr);

				/*sentenceWrappedReading = set.getString("Readings.SentenceWrappedText");
				if (sentenceWrappedReading == null){
					SentenceWrapper wrapper = new SentenceWrapper();
					sentenceWrappedReading = wrapper.prepare(text);
				}*/
				

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return imageMap;
	}
	
	
	public String generateReadingWithTranslation(Integer readingId, String target){
		Integer previousParagraph = 1;
		TreeMap<Integer, String> imageMap = loadImages(readingId);
		Collection<SentenceTranslation> translations = getSentenceTranslationList(readingId, target);
		
		StringBuilder builder = new StringBuilder("<p>");
		StringBuilder translationBuilder = new StringBuilder("");
		WordWrapper wordWrapper = new StandardWordWrapper();
		String image = imageMap.get(1);
		if (image != null){
			builder.insert(0, image);
		}
		for (SentenceTranslation translation: translations){
			String sentence = translation.getProcessedSentence();
			String translationStr = translation.getTranslation();
			translationBuilder.append("<div id=\"d" +  translation.getOrder() + "\" class=\"pop_up\">" + translationStr + "</div>");
			Integer paragraph = translation.getParagraph();
			if (previousParagraph != paragraph){
				previousParagraph = paragraph;
				String imageStr = imageMap.get(paragraph);
				if (imageStr != null){
					builder.append("</p>" + imageStr + "<p>");	
				}
				else{
					builder.append("</p><p>");
				}
			}
			System.out.println(sentence);
			sentence = wordWrapper.wrap(sentence + " ");
			if (!translation.isHeader()){
				sentence = processSentence(sentence.trim(), translation.getOrder());
			}
			builder.append(sentence);

		}
		builder.append("</p>");
		builder.append(translationBuilder);
		return builder.toString();
	}

	public String generateReading(Integer readingId){
		Integer previousParagraph = 1;
		Collection<SentenceTranslation> translations = getSentenceTranslationList(readingId, "es");
		StringBuilder builder = new StringBuilder("<p>");
		StringBuilder translationBuilder = new StringBuilder("");
		for (SentenceTranslation translation: translations){
			String sentence = translation.getSentence();
			//String translationStr = translation.getTranslation();
			//translationBuilder.append("<div id=\"d" +  translation.getOrder() + "\" class=\"pop_up\">" + translationStr + "</div>");
			Integer paragraph = translation.getParagraph();
			if (previousParagraph != paragraph){
				previousParagraph = paragraph;
				builder.append("</p><p>");
			}
			String processedSentence = processSentenceSimple(sentence.trim(), translation.getOrder());
			builder.append(processedSentence);

		}
		builder.append("</p>");
		//builder.append(translationBuilder);
		return builder.toString();
	}


	//mondolgo cosas comunes
	public void generateAndSaveReading(Integer readingId){
		System.out.println("readingId" + readingId);
		String reading = generateReading(readingId);
		org.languageguide.storage.DBConnector connector = org.languageguide.storage.DBConnector.getInstance();
		java.sql.Connection connection = connector.getConnection();
		try{
			String sql = "Update Readings Set SentenceWrappedText = ? WHERE Id = ?";
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, reading);
			statement.setInt(2, readingId);
			statement.executeUpdate();
		}
		catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("translation save failure:" + e.getMessage());
			/*final java.io.Writer result = new java.io.StringWriter();
		    final java.io.PrintWriter printWriter = new java.io.PrintWriter(result);
	    	e.printStackTrace(printWriter);*/



		} 
		finally{
			//			cat.cleanUpStatements();
			if (connector != null){
				connector.close(connection);
			}
		}


	}


	//mondolgo cosas comunes
	public void generateAndSaveReading(Integer readingId, String target){
		System.out.println("readingId" + readingId);
		String translation = generateReadingWithTranslation(readingId, target);
		org.languageguide.storage.DBConnector connector = org.languageguide.storage.DBConnector.getInstance();
		java.sql.Connection connection = connector.getConnection();
		try{
			String sql = "Insert Into TranslatedReading (ReadingId, TargetLanguage, Text, PublishedText) VALUES (?, ?, ?, ?)";
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setInt(1, readingId);
			statement.setString(2, target);
			statement.setString(3, translation);
			statement.setString(4, translation);
			statement.executeUpdate();
		}
		catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("translation save failure:" + e.getMessage());
			/*final java.io.Writer result = new java.io.StringWriter();
		    final java.io.PrintWriter printWriter = new java.io.PrintWriter(result);
	    	e.printStackTrace(printWriter);*/



		} 
		finally{
			//			cat.cleanUpStatements();
			if (connector != null){
				connector.close(connection);
			}
		}


	}

	public void saveTranslations(HttpSession session, HttpServletRequest request, User user){
		String totalStr  = request.getParameter("row_count");
		//System.out.println("totalStr" + totalStr);
		int total = Integer.parseInt(totalStr);
		String readingId = request.getParameter("readingId");
		String target = request.getParameter("target");
		if (readingId == null || totalStr == null || target == null){
			System.out.println("error");
			//return ;
		}
		String message = null;

		org.languageguide.storage.DBConnector connector = org.languageguide.storage.DBConnector.getInstance();
		java.sql.Connection connection = connector.getConnection();	

		Map<Integer, Integer> translationIdMap = new TreeMap<Integer, Integer>();
		Map<Integer, String> translationMap = new TreeMap<Integer, String>();
		try{

			//String getReadingSql = "SELECT Id, SentenceOrder,  LEFT JOIN ReadingSentenceTranslations ON ReadingSentenceTranslations.ReadingSentencesId = ReadingSentences.Id FROM ReadingSentences where ReadingId = ?";	
			String getReadingSql = "SELECT ReadingSentences.Id, ReadingSentences.SentenceOrder, ReadingSentenceTranslations.Translation FROM ReadingSentences LEFT JOIN ReadingSentenceTranslations ON ReadingSentenceTranslations.ReadingSentencesId = ReadingSentences.Id AND ReadingSentenceTranslations.TargetLanguage = ? WHERE ReadingId = ?";
			PreparedStatement stmt = connection.prepareStatement(getReadingSql);
			stmt.setString(1, target);
			stmt.setString(2, readingId);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				Integer translationId = rs.getInt(1);
				Integer sentenceOrder = rs.getInt(2);
				String translation = rs.getString(3);
				translationIdMap.put(sentenceOrder, translationId);
				translationMap.put(sentenceOrder, translation);
			}

			String sql_save = "INSERT INTO ReadingSentenceTranslations (ReadingSentencesId, TargetLanguage, Translation) Values (?, ?, ?)";
			String sql_update = "UPDATE ReadingSentenceTranslations Set Translation = ? WHERE ReadingSentencesId = ? AND TargetLanguage  = ?";

			//String sqlBackup = "INSERT INTO ReadingSentenceTranslationHistory * WHERE TargetLanguage = \"" + targetLanguage + "\" AND ToTranslateId = " + source.getId() +";";

			connection.setAutoCommit(false);
			stmt = connection.prepareStatement(sql_save);
			PreparedStatement stmtUpdate = connection.prepareStatement(sql_update);
			//String ipAddress = request.getRemoteAddr();

			for (int parameterId = 1; parameterId <= total; parameterId++){
				String param = request.getParameter("" + parameterId);

				System.out.println("parameterId" + parameterId + " " + param + "<br/>");
				if (param != null){
					//Integer paramInt = Integer.parseInt(param);
					Integer readingSentenceId = translationIdMap.get(parameterId);
					String translation = translationMap.get(parameterId);
					System.out.println("translation" + translation + "-" + param);
					if (translation == null){
						stmt.setInt(1, readingSentenceId);
						stmt.setString(2, target);
						stmt.setString(3, param);
						stmt.addBatch();
					}
					else if (!param.equals(translation)){
						System.out.println("update" + param + "-" + translation);
						stmtUpdate.setString(1, param);
						stmtUpdate.setInt(2, readingSentenceId);
						stmtUpdate.setString(3, target);						
						stmtUpdate.addBatch();
					}
					//reading.saveSentenceTranslation(connection, parameterId, param, user, ipAddress);				
				}
				//String approvalvalue = request.getParameter("approval" + parameterId);

				//System.out.println("param:" + param);			
				//if (param.indexOf("&#x3f;") == -1) // some browsers are putting weird stuff in translation



				//			parameterId++;
				//		else{
				//		parametersExist = false;
				//}
			}
			stmt.executeBatch();
			stmtUpdate.executeBatch();
			connection.commit();
			//cat.prepare();
			//cat.finishEdit();
			//	user.unlockCategories();
			session.setAttribute("message", "Thank you! Your translations have been saved.");
			//redirect(categoryManager, response, request, category, num, language, target, section);
		}
		catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			final java.io.Writer result = new java.io.StringWriter();
			final java.io.PrintWriter printWriter = new java.io.PrintWriter(result);
			e.printStackTrace(printWriter);
			message = e.getMessage();
			message += e.getCause();
			message += "<br />";
			message += result;
			System.out.println("translation save failure:" + message);
		} 
		finally{
			//			cat.cleanUpStatements();
			if (connector != null){
				connector.close(connection);
			}
		}

	}


	private static LinkedHashMap<Integer, SentenceTranslation> loadSentenceTranslations(Integer readingId, String target){
		LinkedHashMap<Integer, SentenceTranslation> sentenceTranslations = new LinkedHashMap<Integer, SentenceTranslation>();

		String sql_load = "SELECT * FROM ReadingSentences LEFT JOIN ReadingSentenceTranslations ON ReadingSentences.Id = ReadingSentenceTranslations.ReadingSentencesId AND ReadingSentenceTranslations.TargetLanguage = ? WHERE ReadingSentences.ReadingId = ? ORDER BY Paragraph, SentenceOrder";

		//LEFT JOIN Users2 ON Translated.UserId = Users2.userid 
		//System.out.println("sql_load:" + sql_load);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection(false);
		try {

			PreparedStatement stmt = connection.prepareStatement(sql_load);
			stmt.setString(1, target);
			stmt.setInt(2, readingId);
			ResultSet set = stmt.executeQuery();
			while (set.next()){
				String sentence = set.getString("Sentence");
				String translation = set.getString("Translation");
				Integer sentenceOrder = set.getInt("SentenceOrder");
				Integer id = set.getInt("Id");
				Integer paragraph = set.getInt("Paragraph");
				boolean header = set.getBoolean("IsHeader");
				SentenceTranslation source = new SentenceTranslation(sentence, translation, id, paragraph, sentenceOrder, header);
				sentenceTranslations.put(sentenceOrder, source);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			//if (connector != null){
			connector.close(connection);
			//}
		}
		return sentenceTranslations;
	}




	public void store(Connection connection, Map<Integer, SentenceTranslation> sentenceTranslations, Integer readingSentencesId, String targetLanguage, Integer sortingOrder,  String translation,  User user, String ipAddress){
		SentenceTranslation sentenceTranslation = sentenceTranslations.get(sortingOrder);
		if (sentenceTranslation != null){
			sentenceTranslation.storeChange(connection, translation, targetLanguage, user, ipAddress);
		}
	}


	public Collection<SentenceTranslation> getSentenceTranslationList(Integer readingId, String target){

		//int userId = user.getId();
		LinkedHashMap<Integer, SentenceTranslation> sentenceTranslationsMap = loadSentenceTranslations(readingId, target);
		Collection<SentenceTranslation> sentenceTranslations = sentenceTranslationsMap.values();
		/*for (SentenceTranslation translation: sentenceTranslations){
			translation.getSentence();
			translation.getTranslation();
		}*/
		return sentenceTranslations;
	}


	public Collection<SentenceTranslation> getDefinitionList(Integer readingId, String target){

		//int userId = user.getId();
		LinkedHashMap<Integer, SentenceTranslation> sentenceTranslationsMap = loadSentenceTranslations(readingId, target);
		Collection<SentenceTranslation> sentenceTranslations = sentenceTranslationsMap.values();
		/*for (SentenceTranslation translation: sentenceTranslations){
			translation.getSentence();
			translation.getTranslation();
		}*/
		return sentenceTranslations;
	}	

	public LinkedHashMap<Integer, SentenceTranslation>  getSentenceTranslationMap(User user, Integer readingId, String target){
		return loadSentenceTranslations(readingId, target);

		/*for (SentenceTranslation translation: sentenceTranslations){
		translation.getSentence();
		translation.getTranslation();
	}*/
	}

	public static void main(String[] args) {
		//addReading("My Name is Barney", "singlurity", "fr");
		//addParagraghTagsIfMissing("Virtually all of the few known candy poisoning incidents involved parents who poisoned their own children's candy.\r\n\r\nGames and other activities\r\nApple bobbing is a game often played in connection with Halloween.");
		//storeSentencesToTranslate(26);
		//loadSentenceTranslations(26, "fr");
		//new ReadingManager().generateAndSaveReading(78);

		new ReadingManager().storeWordWrappedText(21);
		//	new ReadingManager().storeSentenceWrappedText(76);
		//new ReadingManager().generateAndSaveReading(48, "fr");
		// 
	}

}
